/****** Object: Table [dbo].[SSMA_ACTIVIDADES_T]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_ACTIVIDADES_T]') AND type ='U'))
BEGIN
	DROP TABLE [dbo].[SSMA_ACTIVIDADES_T]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SSMA_ACTIVIDADES_T] (
	[ID_ACTIVIDAD] int IDENTITY(1, 1),
	[DESCRIPCION] varchar(150) NOT NULL,
	[FECHA] datetime NOT NULL,
	[ID_AREA] int NOT NULL,
	[ID_HISTORICO] int NULL,
	[ID_CONDICIONANTE] int NULL,
	[CLAVE_DOC] varchar(100) NULL
) ON [PRIMARY]
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_ACTIVIDADES_T]',
	@OptionName  = 'vardecimal storage format',
	@OptionValue  = 'OFF'
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_ACTIVIDADES_T]',
	@OptionName  = 'table lock on bulk load',
	@OptionValue  = 'OFF'
GO

/****** Object: Table [dbo].[SSMA_AREAS_T]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_AREAS_T]') AND type ='U'))
BEGIN
	DROP TABLE [dbo].[SSMA_AREAS_T]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SSMA_AREAS_T] (
	[ID_AREA] int IDENTITY(1, 1),
	[NOMBRE] varchar(150) NOT NULL,
	[ID_PLANTA] int NOT NULL,
	[ABREVIACION] varchar(3) NOT NULL
) ON [PRIMARY]
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_AREAS_T]',
	@OptionName  = 'vardecimal storage format',
	@OptionValue  = 'OFF'
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_AREAS_T]',
	@OptionName  = 'table lock on bulk load',
	@OptionValue  = 'OFF'
GO

/****** Object: Table [dbo].[SSMA_CONDICIONANTES_T]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_CONDICIONANTES_T]') AND type ='U'))
BEGIN
	DROP TABLE [dbo].[SSMA_CONDICIONANTES_T]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SSMA_CONDICIONANTES_T] (
	[ID_CONDICIONANTE] int IDENTITY(1, 1),
	[DESCRIPCION] varchar(2000) NOT NULL,
	[CLAVE_DOC] varchar(100) NOT NULL,
	[PERIODICIDAD] varchar(5) NULL,
	[ID_HISTORICO] int NOT NULL,
	[DURACION] varchar(5) NULL,
	[RESPONSABLE] varchar(50) NULL,
	[TIPO] varchar(1) NOT NULL,
	[RUTA_GUIA] varchar(200) NULL,
	[FECHA] datetime NULL,
	[NUMERO] varchar(5) NOT NULL,
	[TIPO_GUIA] varchar(25) NULL
) ON [PRIMARY]
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_CONDICIONANTES_T]',
	@OptionName  = 'vardecimal storage format',
	@OptionValue  = 'OFF'
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_CONDICIONANTES_T]',
	@OptionName  = 'table lock on bulk load',
	@OptionValue  = 'OFF'
GO

/****** Object: Table [dbo].[SSMA_DOCUMENTOS_T]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_DOCUMENTOS_T]') AND type ='U'))
BEGIN
	DROP TABLE [dbo].[SSMA_DOCUMENTOS_T]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SSMA_DOCUMENTOS_T] (
	[CLAVE_DOC] varchar(100) NOT NULL,
	[FOLIO] varchar(50) NOT NULL,
	[RUTA] varchar(200) NOT NULL,
	[ID_SECCION] int NOT NULL,
	[DESCRIPCION] varchar(300) NOT NULL,
	[TIPO_DOC] varchar(4) NOT NULL,
	[LUGAR_FISICO] varchar(200) NOT NULL,
	[ID_HISTORICO] int NOT NULL,
	[PRECEDENTE] varchar(100) NULL
) ON [PRIMARY]
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_DOCUMENTOS_T]',
	@OptionName  = 'vardecimal storage format',
	@OptionValue  = 'OFF'
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_DOCUMENTOS_T]',
	@OptionName  = 'table lock on bulk load',
	@OptionValue  = 'OFF'
GO

/****** Object: Table [dbo].[SSMA_HISTORICO_T]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_HISTORICO_T]') AND type ='U'))
BEGIN
	DROP TABLE [dbo].[SSMA_HISTORICO_T]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SSMA_HISTORICO_T] (
	[ID_HISTORICO] int IDENTITY(1, 1),
	[USUARIO_CREA] varchar(25) NOT NULL,
	[FECHA_CREA] datetime NOT NULL,
	[USUARIO_MOD] varchar(25) NOT NULL,
	[FECHA_MOD] datetime NOT NULL,
	[ESTADO] varchar(2) NULL
) ON [PRIMARY]
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_HISTORICO_T]',
	@OptionName  = 'vardecimal storage format',
	@OptionValue  = 'OFF'
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_HISTORICO_T]',
	@OptionName  = 'table lock on bulk load',
	@OptionValue  = 'OFF'
GO

/****** Object: Table [dbo].[SSMA_LISTA_VALORES_T]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_LISTA_VALORES_T]') AND type ='U'))
BEGIN
	DROP TABLE [dbo].[SSMA_LISTA_VALORES_T]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SSMA_LISTA_VALORES_T] (
	[ID_VALOR] int IDENTITY(1, 1),
	[VALOR] varchar(100) NOT NULL,
	[TIPO] varchar(2) NOT NULL
) ON [PRIMARY]
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_LISTA_VALORES_T]',
	@OptionName  = 'vardecimal storage format',
	@OptionValue  = 'OFF'
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_LISTA_VALORES_T]',
	@OptionName  = 'table lock on bulk load',
	@OptionValue  = 'OFF'
GO

/****** Object: Table [dbo].[SSMA_PLANTAS_T]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_PLANTAS_T]') AND type ='U'))
BEGIN
	DROP TABLE [dbo].[SSMA_PLANTAS_T]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SSMA_PLANTAS_T] (
	[ID_PLANTA] int IDENTITY(1, 1),
	[NOMBRE] varchar(150) NOT NULL,
	[ABREVIACION] varchar(150) NOT NULL
) ON [PRIMARY]
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_PLANTAS_T]',
	@OptionName  = 'vardecimal storage format',
	@OptionValue  = 'OFF'
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_PLANTAS_T]',
	@OptionName  = 'table lock on bulk load',
	@OptionValue  = 'OFF'
GO

/****** Object: Table [dbo].[SSMA_SECCIONES_T]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_SECCIONES_T]') AND type ='U'))
BEGIN
	DROP TABLE [dbo].[SSMA_SECCIONES_T]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SSMA_SECCIONES_T] (
	[ID_SECCION] int IDENTITY(1, 1),
	[NOMBRE] varchar(150) NOT NULL,
	[ABREVIACION] varchar(4) NULL,
	[ID_AREA] int NOT NULL,
	[PADRE] int NULL,
	[ID_HISTORICO] int NOT NULL
) ON [PRIMARY]
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_SECCIONES_T]',
	@OptionName  = 'vardecimal storage format',
	@OptionValue  = 'OFF'
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_SECCIONES_T]',
	@OptionName  = 'table lock on bulk load',
	@OptionValue  = 'OFF'
GO

/****** Object: Table [dbo].[SSMA_USUARIOS_T]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_USUARIOS_T]') AND type ='U'))
BEGIN
	DROP TABLE [dbo].[SSMA_USUARIOS_T]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SSMA_USUARIOS_T] (
	[LOGIN] varchar(25) NOT NULL,
	[NOMBRE] varchar(150) NOT NULL,
	[APELLIDOS] varchar(150) NOT NULL,
	[PERFIL] varchar(1) NOT NULL,
	[PUESTO] varchar(50) NULL,
	[ID_AREA] int NOT NULL,
	[CONTRASENA] varbinary(max) NULL,
	[ID_HISTORICO] int NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_USUARIOS_T]',
	@OptionName  = 'vardecimal storage format',
	@OptionValue  = 'OFF'
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_USUARIOS_T]',
	@OptionName  = 'large value types out of row',
	@OptionValue  = 'OFF'
GO

EXECUTE [sp_tableoption]
	@TableNamePattern  = N'[dbo].[SSMA_USUARIOS_T]',
	@OptionName  = 'table lock on bulk load',
	@OptionValue  = 'OFF'
GO

/****** Object: View [dbo].[SSMA_RANDOM_V]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_RANDOM_V]') AND type ='V'))
BEGIN
	DROP VIEW [dbo].[SSMA_RANDOM_V]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[SSMA_RANDOM_V]
AS
SELECT RAND() RANDOM
GO

/****** Object: Function [dbo].[SSMA_ARBOL_SECCIONES_F]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_ARBOL_SECCIONES_F]') AND type IN ('FN', 'IF', 'IS', 'TF', 'FS', 'FT')))
BEGIN
	DROP FUNCTION [dbo].[SSMA_ARBOL_SECCIONES_F]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[SSMA_ARBOL_SECCIONES_F](@INICIO INT)     
returns @temptable TABLE (id_seccion int)     
as     
begin     
	WITH SST(ID_SECCION) AS 
   (SELECT B.ID_SECCION
    FROM SSMA_SECCIONES_T B
    WHERE B.ID_SECCION = @INICIO -- PADRE / INICIO
        UNION ALL
    SELECT SST1.ID_SECCION
    FROM SSMA_SECCIONES_T AS SST1, SST
    WHERE SST1.PADRE = SST.ID_SECCION)
  INSERT INTO @temptable
  SELECT ID_SECCION FROM SST;
return     
end
GO

/****** Object: Function [dbo].[SSMA_PROX_FOLIO_F]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_PROX_FOLIO_F]') AND type IN ('FN', 'IF', 'IS', 'TF', 'FS', 'FT')))
BEGIN
	DROP FUNCTION [dbo].[SSMA_PROX_FOLIO_F]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[SSMA_PROX_FOLIO_F] (@TIPO_DOC VARCHAR(4),  @AREA INT) 
  RETURNS VARCHAR(4)
AS
BEGIN
  DECLARE @NEWF VARCHAR(4);
  --
  SELECT @NEWF =  RIGHT('0000' + CAST(( CASE COUNT(NEWF)
                                  WHEN 0 THEN 1
                                  ELSE NEWF + 1
                                END ) AS VARCHAR(4)),4)
  FROM( SELECT 
          MAX(  CASE ISNUMERIC(FOLIO)
                  WHEN 1 THEN FOLIO
                  ELSE NULL
                END) AS NEWF
        FROM SSMA_DOCUMENTOS_T
        WHERE TIPO_DOC = @TIPO_DOC
          AND ID_SECCION IN (SELECT ID_SECCION FROM SSMA_SECCIONES_T WHERE ID_AREA = @AREA)
          ) A
  GROUP BY NEWF;
  RETURN @NEWF;
END;
GO

/****** Object: Function [dbo].[SSMA_RANDOM_CONTRASENA_F]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_RANDOM_CONTRASENA_F]') AND type IN ('FN', 'IF', 'IS', 'TF', 'FS', 'FT')))
BEGIN
	DROP FUNCTION [dbo].[SSMA_RANDOM_CONTRASENA_F]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[SSMA_RANDOM_CONTRASENA_F]()
RETURNS @tab table(contra varchar(8))
AS
BEGIN
  declare @pool varchar(100);
  declare @counter int;
  declare @pos int;
  declare @rstring varchar(8);

  SET @rstring = '';
  set @pool = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  set @counter = 1;

  while @counter <= 8 begin
    set @counter = @counter + 1;
    SELECT @pos = ceiling(RANDOM*(len(@pool))) FROM SSMA_RANDOM_V;
    --set @pos = ceiling(rand()*(len(@pool)));
    set @rstring = @rstring + substring(@pool, @pos, 1);
  end
  insert into @tab(contra) values(@rstring);
  return
END;
GO

/****** Object: Function [dbo].[SSMA_SPLIT_F]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_SPLIT_F]') AND type IN ('FN', 'IF', 'IS', 'TF', 'FS', 'FT')))
BEGIN
	DROP FUNCTION [dbo].[SSMA_SPLIT_F]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[SSMA_SPLIT_F](@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
	declare @idx int     
	declare @slice varchar(8000)     
    
	select @idx = 1     
		if len(@String)<1 or @String is null  return     
    
	while @idx!= 0     
	begin     
		set @idx = charindex(@Delimiter,@String)     
		if @idx!=0     
			set @slice = left(@String,@idx - 1)     
		else     
			set @slice = @String     
		
		if(len(@slice)>0)
			insert into @temptable(Items) values(@slice)     

		set @String = right(@String,len(@String) - @idx)     
		if len(@String) = 0 break     
	end 
return     
end
GO

/****** Object: Stored Procedure [dbo].[SSMA_ALTER_ACTIVIDAD_P]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_ALTER_ACTIVIDAD_P]') AND type IN ('P', 'RF', 'PC')))
BEGIN
	DROP PROCEDURE [dbo].[SSMA_ALTER_ACTIVIDAD_P]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

-- -----------------------------------------------------
-- INSERT O UPDATE Actividades
-- -----------------------------------------------------  
CREATE PROCEDURE [dbo].[SSMA_ALTER_ACTIVIDAD_P]
@ACCION varchar(1), 
@USUARIO varchar(25), 
@ID_ACTIVIDAD int, 
@DESCRIPCION varchar(150), 
@FE VARCHAR(10), 
@ESTADO varchar(2), 
@ID_AREA int, 
@ID_CONDICIONANTE int,
@CLAVE_DOC VARCHAR(100)
WITH EXEC AS CALLER
AS
BEGIN TRANSACTION
  SET NOCOUNT ON;
  DECLARE @FECHA DATETIME;
  SET @FECHA = DATEADD(MINUTE,1439,CONVERT(DATETIME,@FE,103));
  IF(@ACCION = 'I') BEGIN
    INSERT INTO SSMA_HISTORICO_T(USUARIO_CREA, FECHA_CREA, USUARIO_MOD, FECHA_MOD,ESTADO)
    VALUES(
      @USUARIO,
      GETDATE(),
      @USUARIO,
      GETDATE(),
      'PD'
    );
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error levantando historico.', 16, 1)
      RETURN
    END
    --
    DECLARE @ID_HISTORICO INT;
    SELECT @ID_HISTORICO = MAX(ID_HISTORICO) FROM SSMA_HISTORICO_T;
    --
    INSERT INTO SSMA_ACTIVIDADES_T (DESCRIPCION,FECHA,ID_HISTORICO,ID_AREA,ID_CONDICIONANTE,CLAVE_DOC)
    VALUES (@DESCRIPCION,@FECHA,@ID_HISTORICO,@ID_AREA,@ID_CONDICIONANTE,@CLAVE_DOC);
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error insertando actividad.', 16, 1)
      RETURN
    END
  END
  ELSE BEGIN
    UPDATE  SSMA_ACTIVIDADES_T
    SET     DESCRIPCION=@DESCRIPCION,
            CLAVE_DOC = @CLAVE_DOC
    WHERE   ID_ACTIVIDAD = @ID_ACTIVIDAD;
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error actualizando actividad', 16, 1)
      RETURN
    END
    --
    UPDATE  SSMA_HISTORICO_T
    SET     USUARIO_MOD = @USUARIO,
            FECHA_MOD = GETDATE(),
            ESTADO = @ESTADO
    WHERE   ID_HISTORICO IN (SELECT ID_HISTORICO 
                              FROM SSMA_ACTIVIDADES_T 
                              WHERE ID_ACTIVIDAD = @ID_ACTIVIDAD);
    IF @ESTADO = 'IN' BEGIN
      UPDATE  SSMA_HISTORICO_T
      SET     USUARIO_MOD = @USUARIO,
              FECHA_MOD = GETDATE(),
              ESTADO = @ESTADO
      WHERE   ID_HISTORICO IN (SELECT ID_HISTORICO 
                                FROM SSMA_DOCUMENTOS_T 
                                WHERE CLAVE_DOC IN (SELECT CLAVE_DOC 
                                                    FROM SSMA_ACTIVIDADES_T 
                                                    WHERE ID_ACTIVIDAD = @ID_ACTIVIDAD));
    END;
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error actualizando historico', 16, 1)
      RETURN
    END
  END
COMMIT;
GO

/****** Object: Stored Procedure [dbo].[SSMA_ALTER_CONDICIONANTE_P]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_ALTER_CONDICIONANTE_P]') AND type IN ('P', 'RF', 'PC')))
BEGIN
	DROP PROCEDURE [dbo].[SSMA_ALTER_CONDICIONANTE_P]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

-- -----------------------------------------------------
-- INSERT O UPDATE Condicionante
-- -----------------------------------------------------  
create PROCEDURE [dbo].[SSMA_ALTER_CONDICIONANTE_P]
@ACCION varchar(1), 
@USUARIO varchar(25), 
@ID_CONDICIONANTE int, 
@DESCRIPCION varchar(2000), 
@CLAVE_DOC varchar(100), 
@PERIODICIDAD varchar(5), 
@ESTADO varchar(2), 
@F_CUMPLIMIENTO varchar(10), 
@DURACION varchar(5), 
@RESPONSABLE varchar(59), 
@TIPO varchar(1), 
@RUTA_GUIA varchar(200), 
@NUMERO VARCHAR(5), 
@TIPO_GUIA varchar(25)
WITH EXEC AS CALLER
AS
BEGIN TRANSACTION
  SET NOCOUNT ON;
  DECLARE @ID_AREA INT;
  DECLARE @ID_HISTORICO INT;
  DECLARE @FECHA_CUMPLIMIENTO DATETIME;
  DECLARE @FECHA_ACT VARCHAR(10);
  DECLARE @OLD_FE VARCHAR (10);
  DECLARE @OLD_DUR VARCHAR(5);
  DECLARE @OLD_PER VARCHAR(5);
  DECLARE @PER INT;
  DECLARE @PER_MAG VARCHAR(1);
  DECLARE @DUR INT;
  DECLARE @DUR_MAG VARCHAR(1);
  DECLARE @FECHA_FINAL DATETIME;
  
  IF(@ACCION = 'I') BEGIN
    INSERT INTO SSMA_HISTORICO_T(USUARIO_CREA, FECHA_CREA, USUARIO_MOD, FECHA_MOD, ESTADO)
    VALUES(
      @USUARIO,
      GETDATE(),
      @USUARIO,
      GETDATE(),
      'CR'
    );
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error levantando historico.', 16, 1)
      RETURN
    END
    --
    SELECT @ID_HISTORICO = MAX(ID_HISTORICO) FROM SSMA_HISTORICO_T;
    --
    INSERT INTO SSMA_CONDICIONANTES_T (DESCRIPCION,CLAVE_DOC,PERIODICIDAD, ID_HISTORICO, DURACION, RESPONSABLE, TIPO, RUTA_GUIA,FECHA,NUMERO, TIPO_GUIA)
    VALUES (@DESCRIPCION, @CLAVE_DOC, @PERIODICIDAD, @ID_HISTORICO, @DURACION, @RESPONSABLE, @TIPO, @RUTA_GUIA,DATEADD(MINUTE,1439,CONVERT(DATETIME,@F_CUMPLIMIENTO,103)), @NUMERO, @TIPO_GUIA); 
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error levantando condicionante.', 16, 1)
      RETURN
    END
    IF NOT(@F_CUMPLIMIENTO IS NULL) BEGIN
      
      SELECT @ID_CONDICIONANTE = MAX(ID_CONDICIONANTE) FROM SSMA_CONDICIONANTES_T;
      SELECT  @ID_AREA = ID_AREA FROM    SSMA_SECCIONES_T WHERE   ID_SECCION IN ( SELECT  ID_SECCION FROM    SSMA_DOCUMENTOS_T WHERE   CLAVE_DOC = @CLAVE_DOC);
      IF NOT(@PERIODICIDAD IS NULL OR @DURACION IS NULL) BEGIN
        /*
        --
        SET @FECHA_CUMPLIMIENTO = CONVERT(DATETIME,@F_CUMPLIMIENTO,103);
        SET @i = 0;
        SET @CANT_ACT = FLOOR(@DURACION/@PERIODICIDAD);
        --
        WHILE (@i < @CANT_ACT) BEGIN
          PRINT(@i);
          SET @FECHA_ACT = CONVERT(VARCHAR(10),DATEADD(MONTH, (@PERIODICIDAD * @I), @FECHA_CUMPLIMIENTO),103);
          EXEC SSMA_ALTER_ACTIVIDAD_P 'I', @USUARIO, 0, @DESCRIPCION, @FECHA_ACT, 'PD', @ID_AREA, @ID_CONDICIONANTE, NULL;
          SET @i = @i + 1;
        END
        */
        -----------------------------------------------------TEST
        SET @PER = CONVERT(INT, LEFT(@PERIODICIDAD, LEN(@PERIODICIDAD)-1 ));
        SET @PER_MAG = RIGHT(@PERIODICIDAD,1);
        SET @DUR = CONVERT(INT, LEFT(@DURACION, LEN(@DURACION)-1 ));
        SET @DUR_MAG = RIGHT(@DURACION,1);
        SET @FECHA_CUMPLIMIENTO = CONVERT(DATETIME,@F_CUMPLIMIENTO,103);
        SET @FECHA_FINAL =  CASE @DUR_MAG 
                              WHEN 'D' THEN DATEADD(DAY, @DUR, @FECHA_CUMPLIMIENTO) 
                              WHEN 'W' THEN DATEADD(WEEK, @DUR, @FECHA_CUMPLIMIENTO) 
                              WHEN 'M' THEN DATEADD(MONTH, @DUR, @FECHA_CUMPLIMIENTO)
                              WHEN 'Y' THEN DATEADD(YEAR, @DUR, @FECHA_CUMPLIMIENTO) 
                            END;
        WHILE @FECHA_CUMPLIMIENTO < @FECHA_FINAL BEGIN
          SET @FECHA_ACT = CONVERT(VARCHAR(10),@FECHA_CUMPLIMIENTO,103);
          EXEC SSMA_ALTER_ACTIVIDAD_P 'I', @USUARIO, 0, @DESCRIPCION, @FECHA_ACT, 'PD', @ID_AREA, @ID_CONDICIONANTE, NULL;
          SET @FECHA_CUMPLIMIENTO = CASE @PER_MAG 
                                      WHEN 'D' THEN DATEADD(DAY, @PER, @FECHA_CUMPLIMIENTO)
                                      WHEN 'W' THEN DATEADD(WEEK, @PER, @FECHA_CUMPLIMIENTO) 
                                      WHEN 'M' THEN DATEADD(MONTH, @PER, @FECHA_CUMPLIMIENTO)
                                      WHEN 'Y' THEN DATEADD(YEAR, @PER, @FECHA_CUMPLIMIENTO) 
                                    END;
        END
        -----------------------------------------------------TSET
      END
      ELSE BEGIN
        EXEC SSMA_ALTER_ACTIVIDAD_P 'I', @USUARIO, 0, @DESCRIPCION, @F_CUMPLIMIENTO, 'PD', @ID_AREA, @ID_CONDICIONANTE, NULL;
      END
    END
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error levantando actividades', 16, 1)
      RETURN
    END
  END
  ELSE BEGIN

    SELECT  @OLD_FE = CONVERT(VARCHAR(10),FECHA,103), @OLD_DUR = DURACION, @OLD_PER = PERIODICIDAD
      FROM  SSMA_CONDICIONANTES_T
     WHERE  ID_CONDICIONANTE = @ID_CONDICIONANTE;
    
    UPDATE  SSMA_CONDICIONANTES_T
    SET     DESCRIPCION = @DESCRIPCION,
            --CLAVE_DOC = @CLAVE_DOC,
            PERIODICIDAD = @PERIODICIDAD,
            DURACION = @DURACION,
            RESPONSABLE = @RESPONSABLE,
            TIPO = @TIPO,
            RUTA_GUIA = @RUTA_GUIA,
            TIPO_GUIA = @TIPO_GUIA,
            FECHA = DATEADD(MINUTE,1439,CONVERT(DATETIME,@F_CUMPLIMIENTO,103)),
            NUMERO = @NUMERO
    WHERE   ID_CONDICIONANTE = @ID_CONDICIONANTE;
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error actualizando condicionante.', 16, 1)
      RETURN
    END
    
    IF (@OLD_DUR != @DURACION OR @OLD_PER != @PERIODICIDAD OR @OLD_FE != @F_CUMPLIMIENTO) BEGIN
      DELETE FROM SSMA_ACTIVIDADES_T WHERE ID_CONDICIONANTE = @ID_CONDICIONANTE;
      EXEC SSMA_LIMPIAR_HISTORICO_P;
      IF NOT(@F_CUMPLIMIENTO IS NULL) BEGIN
        SELECT  @ID_AREA = ID_AREA FROM    SSMA_SECCIONES_T WHERE   ID_SECCION IN ( SELECT  ID_SECCION FROM    SSMA_DOCUMENTOS_T WHERE   CLAVE_DOC = @CLAVE_DOC);
        IF NOT(@PERIODICIDAD IS NULL OR @DURACION IS NULL) BEGIN
          --
          /*
          SET @FECHA_CUMPLIMIENTO = CONVERT(DATETIME,@F_CUMPLIMIENTO,103);
          SET @i = 0;
          SET @CANT_ACT = FLOOR(@DURACION/@PERIODICIDAD);
          --
          WHILE (@i < @CANT_ACT) BEGIN
            PRINT(@i);
            SET @FECHA_ACT = CONVERT(VARCHAR(10),DATEADD(MONTH, (@PERIODICIDAD * @I), @FECHA_CUMPLIMIENTO),103);
            EXEC SSMA_ALTER_ACTIVIDAD_P 'I', @USUARIO, 0, @DESCRIPCION, @FECHA_ACT, 'PD', @ID_AREA, @ID_CONDICIONANTE, NULL;
            SET @i = @i + 1;
          END
          */
          -----------------------------------------------------TEST
          SET @PER = CONVERT(INT, LEFT(@PERIODICIDAD, LEN(@PERIODICIDAD)-1 ));
          SET @PER_MAG = RIGHT(@PERIODICIDAD,1);
          SET @DUR = CONVERT(INT, LEFT(@DURACION, LEN(@DURACION)-1 ));
          SET @DUR_MAG = RIGHT(@DURACION,1);
          SET @FECHA_CUMPLIMIENTO = CONVERT(DATETIME,@F_CUMPLIMIENTO,103);
          SET @FECHA_FINAL =  CASE @DUR_MAG 
                                WHEN 'D' THEN DATEADD(DAY, @DUR, @FECHA_CUMPLIMIENTO) 
                                WHEN 'W' THEN DATEADD(WEEK, @DUR, @FECHA_CUMPLIMIENTO) 
                                WHEN 'M' THEN DATEADD(MONTH, @DUR, @FECHA_CUMPLIMIENTO)
                                WHEN 'Y' THEN DATEADD(YEAR, @DUR, @FECHA_CUMPLIMIENTO) 
                              END;
          WHILE @FECHA_CUMPLIMIENTO < @FECHA_FINAL BEGIN
            SET @FECHA_ACT = CONVERT(VARCHAR(10),@FECHA_CUMPLIMIENTO,103);
            EXEC SSMA_ALTER_ACTIVIDAD_P 'I', @USUARIO, 0, @DESCRIPCION, @FECHA_ACT, 'PD', @ID_AREA, @ID_CONDICIONANTE, NULL;
            SET @FECHA_CUMPLIMIENTO = CASE @PER_MAG 
                                        WHEN 'D' THEN DATEADD(DAY, @PER, @FECHA_CUMPLIMIENTO) 
                                        WHEN 'W' THEN DATEADD(WEEK, @PER, @FECHA_CUMPLIMIENTO) 
                                        WHEN 'M' THEN DATEADD(MONTH, @PER, @FECHA_CUMPLIMIENTO)
                                        WHEN 'Y' THEN DATEADD(YEAR, @PER, @FECHA_CUMPLIMIENTO) 
                                      END;
          END
          -----------------------------------------------------TSET
        END
        ELSE BEGIN
          EXEC SSMA_ALTER_ACTIVIDAD_P 'I', @USUARIO, 0, @DESCRIPCION, @F_CUMPLIMIENTO, 'PD', @ID_AREA, @ID_CONDICIONANTE, NULL;
        END
      END
    END
    ELSE BEGIN
      UPDATE  SSMA_ACTIVIDADES_T
      SET     DESCRIPCION = @DESCRIPCION
      WHERE   ID_CONDICIONANTE = @ID_CONDICIONANTE;
    END
    --
    UPDATE  SSMA_HISTORICO_T
    SET     USUARIO_MOD = @USUARIO,
            FECHA_MOD = GETDATE(),
            ESTADO = @ESTADO
    WHERE   ID_HISTORICO IN (SELECT ID_HISTORICO FROM SSMA_CONDICIONANTES_T WHERE ID_CONDICIONANTE = @ID_CONDICIONANTE);
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error levantando historico.', 16, 1)
      RETURN
    END
    IF @ESTADO = 'IN' BEGIN
      UPDATE  SSMA_HISTORICO_T
      SET     USUARIO_MOD = @USUARIO,
              FECHA_MOD = GETDATE(),
              ESTADO = @ESTADO
      WHERE   ID_HISTORICO IN (SELECT ID_HISTORICO FROM SSMA_ACTIVIDADES_T WHERE ID_CONDICIONANTE = @ID_CONDICIONANTE);
      UPDATE  SSMA_HISTORICO_T
      SET     USUARIO_MOD = @USUARIO,
              FECHA_MOD = GETDATE(),
              ESTADO = @ESTADO
      WHERE   ID_HISTORICO IN (SELECT ID_HISTORICO FROM SSMA_DOCUMENTOS_T 
                                WHERE CLAVE_DOC IN( SELECT CLAVE_DOC FROM SSMA_ACTIVIDADES_T WHERE ID_CONDICIONANTE = @ID_CONDICIONANTE));
    END
  END
  exec SSMA_CAMBIO_EDO_ACT_P;
COMMIT;
GO

/****** Object: Stored Procedure [dbo].[SSMA_ALTER_DOCUMENTO_P]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_ALTER_DOCUMENTO_P]') AND type IN ('P', 'RF', 'PC')))
BEGIN
	DROP PROCEDURE [dbo].[SSMA_ALTER_DOCUMENTO_P]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SSMA_ALTER_DOCUMENTO_P]
@ACCION varchar(1), 
@USUARIO varchar(25), 
@CLAVE_DOC varchar(100), 
@FOLIO varchar(50), 
@RUTA varchar(200), 
@ID_SECCION int, 
@DESCRIPCION varchar(300), 
@TIPO_DOC varchar(4), 
@LUGAR_FISICO varchar(200), 
@ESTADO varchar(2),
@PRECEDENTE VARCHAR(100)
WITH EXEC AS CALLER
AS
BEGIN TRANSACTION
  --SET NOCOUNT ON;
  IF(@ACCION = 'I') BEGIN
    INSERT INTO SSMA_HISTORICO_T(USUARIO_CREA, FECHA_CREA, USUARIO_MOD, FECHA_MOD, ESTADO)
    VALUES(
      @USUARIO,
      GETDATE(),
      @USUARIO,
      GETDATE(),
      'CR'
    );
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error levantando historico.', 16, 1)
      RETURN
    END
    --
    DECLARE @ID_HISTORICO INT;
    SELECT @ID_HISTORICO = MAX(ID_HISTORICO) FROM SSMA_HISTORICO_T;
    --
    INSERT INTO SSMA_DOCUMENTOS_T (
      CLAVE_DOC,
      FOLIO,
      RUTA,
      ID_SECCION,
      DESCRIPCION,
      TIPO_DOC,
      LUGAR_FISICO,
      ID_HISTORICO,
      PRECEDENTE
    )
    VALUES (  
      @CLAVE_DOC,
      @FOLIO,
      @RUTA,
      @ID_SECCION,
      @DESCRIPCION,
      @TIPO_DOC,
      @LUGAR_FISICO,
      @ID_HISTORICO,
      @PRECEDENTE
    );
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error insertando Documento', 16, 1)
      RETURN
    END
  END
  ELSE BEGIN
    UPDATE  SSMA_DOCUMENTOS_T
    SET     --FOLIO = @FOLIO,
            RUTA = @RUTA,
            --ID_SECCION = @ID_SECCION,
            DESCRIPCION = @DESCRIPCION,
            --TIPO_DOC = @TIPO_DOC,
            LUGAR_FISICO = @LUGAR_FISICO,
            PRECEDENTE = @PRECEDENTE
    WHERE   CLAVE_DOC = @CLAVE_DOC;
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error actualizando Documento', 16, 1)
      RETURN
    END
    --
    UPDATE  SSMA_HISTORICO_T
    SET     USUARIO_MOD = @USUARIO,
            FECHA_MOD = GETDATE(),
            ESTADO = @ESTADO
    WHERE   ID_HISTORICO IN (SELECT ID_HISTORICO FROM SSMA_DOCUMENTOS_T WHERE CLAVE_DOC = @CLAVE_DOC);
        --
    IF @ESTADO = 'IN' BEGIN
      UPDATE SSMA_HISTORICO_T
      SET USUARIO_MOD = @USUARIO,
          FECHA_MOD = GETDATE(),
          ESTADO = @ESTADO
      WHERE ID_HISTORICO IN (SELECT ID_HISTORICO 
                              FROM SSMA_CONDICIONANTES_T
                              WHERE CLAVE_DOC = @CLAVE_DOC);
      UPDATE SSMA_HISTORICO_T
      SET USUARIO_MOD = @USUARIO,
          FECHA_MOD = GETDATE(),
          ESTADO = @ESTADO
      WHERE ID_HISTORICO IN (SELECT ID_HISTORICO 
                              FROM SSMA_ACTIVIDADES_T
                              WHERE ID_CONDICIONANTE IN ( SELECT ID_CONDICIONANTE 
                                                            FROM SSMA_CONDICIONANTES_T
                                                           WHERE CLAVE_DOC = @CLAVE_DOC));
      UPDATE SSMA_HISTORICO_T
      SET USUARIO_MOD = @USUARIO,
          FECHA_MOD = GETDATE(),
          ESTADO = @ESTADO
      WHERE ID_HISTORICO IN (SELECT ID_HISTORICO 
                              FROM SSMA_DOCUMENTOS_T
                              WHERE CLAVE_DOC IN ( SELECT CLAVE_DOC 
                                                    FROM SSMA_ACTIVIDADES_T 
                                                    WHERE ID_CONDICIONANTE IN ( SELECT ID_CONDICIONANTE 
                                                                                FROM SSMA_CONDICIONANTES_T
                                                                                WHERE CLAVE_DOC = @CLAVE_DOC)));
    END
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error actualizando Historico', 16, 1)
      RETURN
    END
  END
COMMIT;
GO

/****** Object: Stored Procedure [dbo].[SSMA_ALTER_SECCION_P]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_ALTER_SECCION_P]') AND type IN ('P', 'RF', 'PC')))
BEGIN
	DROP PROCEDURE [dbo].[SSMA_ALTER_SECCION_P]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

-- -----------------------------------------------------
-- INSERT O UPDATE Secciones
-- -----------------------------------------------------  
CREATE PROCEDURE [dbo].[SSMA_ALTER_SECCION_P]
@ACCION varchar(1), 
@USUARIO varchar(25), 
@ID_SECCION int, 
@NOMBRE varchar(150), 
@ABREVIACION varchar(150), 
@ID_AREA int, 
@PADRE int,
@ESTADO VARCHAR(2)
WITH EXEC AS CALLER
AS
BEGIN TRANSACTION
  SET NOCOUNT ON;
  IF(@ACCION = 'I') BEGIN
    INSERT INTO SSMA_HISTORICO_T(USUARIO_CREA, FECHA_CREA, USUARIO_MOD, FECHA_MOD, ESTADO)
    VALUES(
      @USUARIO,
      GETDATE(),
      @USUARIO,
      GETDATE(),
      'CR'
    );
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error levantando historico.', 16, 1)
      RETURN
    END
    --
    DECLARE @ID_HISTORICO INT;
    SELECT @ID_HISTORICO = MAX(ID_HISTORICO) FROM SSMA_HISTORICO_T;
    --
    INSERT INTO SSMA_SECCIONES_T (NOMBRE,ABREVIACION,ID_AREA,PADRE,ID_HISTORICO)
    VALUES (@NOMBRE,@ABREVIACION,@ID_AREA,@PADRE,@ID_HISTORICO);
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error levantando seccion.', 16, 1)
      RETURN
    END
  END
  
  ELSE BEGIN
    UPDATE  SSMA_SECCIONES_T
    SET     NOMBRE = @NOMBRE,
            ABREVIACION = @ABREVIACION,
            ID_AREA = @ID_AREA,
            PADRE = @PADRE
    WHERE   ID_SECCION = @ID_SECCION;
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error actualizando seccion.', 16, 1)
      RETURN
    END
    --
    UPDATE  SSMA_HISTORICO_T
    SET     USUARIO_MOD = @USUARIO,
            FECHA_MOD = GETDATE(),
            ESTADO = @ESTADO
    WHERE   ID_HISTORICO IN (SELECT ID_HISTORICO 
                              FROM SSMA_SECCIONES_T 
                              WHERE ID_SECCION = @ID_SECCION);
    IF @ESTADO = 'IN' BEGIN
      
      DECLARE @hijos TABLE (
        ROWNUM INTEGER,
        ID_SECCION INTEGER,	
        NOMBRE VARCHAR(150),	
        ABREVIACION VARCHAR(4),	
        ID_AREA INTEGER,	
        PADRE INTEGER,
        ID_HISTORICO INTEGER);
      INSERT INTO @hijos 
      SELECT ROW_NUMBER() OVER(ORDER BY SST.ID_SECCION DESC) AS ROWNUM, SST.* FROM SSMA_SECCIONES_T SST WHERE PADRE = @ID_SECCION;
      
      DECLARE @ID_SECCION2 INTEGER;
      DECLARE @NOMBRE2 VARCHAR(150);
      DECLARE @ABREVIACION2 VARCHAR(4);
      DECLARE @ID_AREA2 INTEGER;
      DECLARE @PADRE2 INTEGER;
      DECLARE @I INTEGER;
      SELECT @I = CASE MAX(ROWNUM) WHEN NULL THEN 0 ELSE MAX(ROWNUM) END FROM @hijos;
      WHILE @I > 0 BEGIN
        SELECT
          @ID_SECCION2 = ID_SECCION,
          @NOMBRE2 = NOMBRE,
          @ABREVIACION2 = ABREVIACION,
          @ID_AREA2 = ID_AREA,
          @PADRE2 = PADRE
        FROM @hijos 
        WHERE ROWNUM = @I
        EXEC SSMA_ALTER_SECCION_P 'U', @USUARIO, @ID_SECCION2, @NOMBRE2, @ABREVIACION2, @ID_AREA2, @PADRE2, 'IN';
        DELETE FROM @hijos WHERE ROWNUM = @I;
        SET @I = @I - 1;
      END;
      --
      UPDATE  SSMA_HISTORICO_T
      SET     USUARIO_MOD = @USUARIO,
              FECHA_MOD = GETDATE(),
              ESTADO = @ESTADO
      WHERE   ID_HISTORICO IN (SELECT ID_HISTORICO
                                FROM  SSMA_DOCUMENTOS_T
                                WHERE ID_SECCION = @ID_SECCION);
      UPDATE  SSMA_HISTORICO_T
      SET     USUARIO_MOD = @USUARIO,
              FECHA_MOD = GETDATE(),
              ESTADO = @ESTADO
      WHERE   ID_HISTORICO IN (SELECT ID_HISTORICO
                                FROM  SSMA_CONDICIONANTES_T
                                WHERE CLAVE_DOC IN (SELECT CLAVE_DOC
                                                     FROM  SSMA_DOCUMENTOS_T
                                                     WHERE ID_SECCION = @ID_SECCION));
      UPDATE  SSMA_HISTORICO_T
      SET     USUARIO_MOD = @USUARIO,
              FECHA_MOD = GETDATE(),
              ESTADO = @ESTADO
      WHERE   ID_HISTORICO IN (SELECT ID_HISTORICO
                                FROM SSMA_ACTIVIDADES_T
                                WHERE ID_CONDICIONANTE IN (SELECT ID_CONDICIONANTE
                                                          FROM  SSMA_CONDICIONANTES_T
                                                          WHERE CLAVE_DOC IN (SELECT CLAVE_DOC
                                                                               FROM  SSMA_DOCUMENTOS_T
                                                                               WHERE ID_SECCION = @ID_SECCION)));
      UPDATE  SSMA_HISTORICO_T
      SET     USUARIO_MOD = @USUARIO,
              FECHA_MOD = GETDATE(),
              ESTADO = @ESTADO
      WHERE   ID_HISTORICO IN (SELECT ID_HISTORICO 
                                FROM SSMA_DOCUMENTOS_T
                                WHERE CLAVE_DOC IN (SELECT CLAVE_DOC
                                                    FROM SSMA_ACTIVIDADES_T
                                                    WHERE ID_CONDICIONANTE IN (SELECT ID_CONDICIONANTE
                                                                              FROM  SSMA_CONDICIONANTES_T
                                                                              WHERE CLAVE_DOC IN (SELECT CLAVE_DOC
                                                                                                   FROM  SSMA_DOCUMENTOS_T
                                                                                                   WHERE ID_SECCION = @ID_SECCION))));
    END
    
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error actualizando historico.', 16, 1)
      RETURN
    END
  END
COMMIT;
GO

/****** Object: Stored Procedure [dbo].[SSMA_ALTER_USUARIO_P]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_ALTER_USUARIO_P]') AND type IN ('P', 'RF', 'PC')))
BEGIN
	DROP PROCEDURE [dbo].[SSMA_ALTER_USUARIO_P]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

-- -----------------------------------------------------
-- INSERT O UPDATE Usuarios
-- -----------------------------------------------------  
CREATE PROCEDURE [dbo].[SSMA_ALTER_USUARIO_P]
  --ARGUMENTOS POR DEFECTO
  @ACCION VARCHAR(1),
  @USUARIO VARCHAR(25),
  --ARGUMENTOS DE LA TABLA
  @LOGIN VARCHAR(25),
  @NOMBRE VARCHAR(150),
  @APELLIDOS VARCHAR(150),
  @PERFIL VARCHAR(1),
  @PUESTO VARCHAR(50),
  @AREA INT,
  @CONTRA VARCHAR(50),
  @ESTADO VARCHAR(2)
AS
BEGIN TRANSACTION
  SET NOCOUNT ON;
  DECLARE @CONTRASENA varbinary(MAX);
  SET @CONTRASENA = HashBytes('SHA1', CONVERT(nvarchar,@CONTRA));
  IF(@ACCION = 'I') BEGIN
    INSERT INTO SSMA_HISTORICO_T(USUARIO_CREA, FECHA_CREA, USUARIO_MOD, FECHA_MOD, ESTADO)
    VALUES(
      @USUARIO,
      GETDATE(),
      @USUARIO,
      GETDATE(),
      'CR'
    );
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error levantando historico.', 16, 1)
      RETURN
    END
    --
    DECLARE @ID_HISTORICO INT;
    SELECT @ID_HISTORICO = MAX(ID_HISTORICO) FROM SSMA_HISTORICO_T;
    --
    INSERT INTO SSMA_USUARIOS_T (
      LOGIN, 
      NOMBRE, 
      APELLIDOS,
      PERFIL,
      PUESTO,
      ID_AREA,
      CONTRASENA,
      ID_HISTORICO
    )
    VALUES (
      @LOGIN, 
      @NOMBRE, 
      @APELLIDOS,
      @PERFIL,
      @PUESTO,
      @AREA,
      @CONTRASENA,
      @ID_HISTORICO
    );  
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error levantando usuario.', 16, 1)
      RETURN
    END
  END
  ELSE BEGIN
    UPDATE  SSMA_USUARIOS_T
    SET     NOMBRE = @NOMBRE, 
            APELLIDOS = @APELLIDOS,
            PERFIL = @PERFIL,
            PUESTO = @PUESTO,
            ID_AREA = @AREA
            --CONTRASENA = @CONTRASENA
    WHERE   LOGIN = @LOGIN;
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error actualizando usuario', 16, 1)
      RETURN
    END
    --
    UPDATE  SSMA_HISTORICO_T
    SET     USUARIO_MOD = @USUARIO,
            FECHA_MOD = GETDATE()
    WHERE   ID_HISTORICO IN (SELECT ID_HISTORICO FROM SSMA_USUARIOS_T WHERE LOGIN = @LOGIN);
    IF @@ERROR <> 0 BEGIN
      ROLLBACK
      RAISERROR ('Error actualizando historico.', 16, 1)
      RETURN
    END
  END
COMMIT;
GO

/****** Object: Stored Procedure [dbo].[SSMA_BUSQUEDA_P]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_BUSQUEDA_P]') AND type IN ('P', 'RF', 'PC')))
BEGIN
	DROP PROCEDURE [dbo].[SSMA_BUSQUEDA_P]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

-- -----------------------------------------------------
-- BUSQUEDA EN BD
-- -----------------------------------------------------  
CREATE PROCEDURE [dbo].[SSMA_BUSQUEDA_P]
  --ARGUMENTOS POR DEFECTO
  @CRITERIO VARCHAR(250),
  @AREA INT
AS
BEGIN
  SET NOCOUNT ON;
    
  DECLARE @COINCIDENCIA INT;
  DECLARE @CRITS TABLE(ITEMS VARCHAR(8000));
  SET @CRITERIO = LTRIM(RTRIM(@CRITERIO));
  
  INSERT INTO @CRITS (ITEMS) 
    ( SELECT DISTINCT ITEMS 
      FROM SSMA_SPLIT_F(@CRITERIO, ' ') SPT
      WHERE ITEMS NOT IN (  SELECT VALOR FROM SSMA_LISTA_VALORES_T WHERE TIPO = 'SW'));  
    
  SELECT @COINCIDENCIA = COUNT(1) FROM @CRITS;
  
  SELECT  TIPO, 
          CASE 
            WHEN LEN(DESCRIPCION) > 25  THEN CONVERT(VARCHAR(25),DESCRIPCION) + '...'
            ELSE DESCRIPCION
          END + ' (' + IDENTIFICADOR + ')' AS DESCRIPCION, 
          VINCULO, 
          CASE COUNT(1) WHEN @COINCIDENCIA THEN 1 ELSE 0 END  AS COINCIDENCIA 
  FROM (
          SELECT  'DOC' 
                    AS TIPO,
                  CLAVE_DOC 
                    AS IDENTIFICADOR,
                  DESCRIPCION 
                    AS DESCRIPCION,
                  RUTA 
                    AS VINCULO,
                  (SELECT FECHA_CREA FROM SSMA_HISTORICO_T SHT WHERE SHT.ID_HISTORICO = SDT.ID_HISTORICO) 
                    AS FECHA
          FROM    SSMA_DOCUMENTOS_T 
                    SDT,
                  @CRITS --(SELECT DISTINCT ITEMS FROM SSMA_SPLIT_F(@CRITERIO, ' '))
                    SPT
          WHERE   ID_SECCION IN ( SELECT ID_SECCION 
                                  FROM SSMA_SECCIONES_T 
                                  WHERE ID_AREA = @AREA )
            AND   ID_HISTORICO NOT IN (SELECT ID_HISTORICO FROM SSMA_HISTORICO_T WHERE ESTADO = 'IN')
            AND   (
                        UPPER(DESCRIPCION) LIKE '%'+UPPER(SPT.ITEMS)+'%'
                    OR  UPPER(CLAVE_DOC) LIKE '%'+UPPER(SPT.ITEMS)+'%'
                  )
          --        
            UNION ALL
          --
            SELECT  'ACT' 
                      AS TIPO,
                    CONVERT(VARCHAR(24),ID_ACTIVIDAD) 
                      AS IDENTIFICADOR,
                    DESCRIPCION 
                      AS DESCRIPCION,
                    CONVERT(VARCHAR(200),ID_ACTIVIDAD)
                      AS VINCULO,
                    FECHA 
                      AS FECHA 
            FROM    SSMA_ACTIVIDADES_T 
                      SDT,
                    @CRITS --(SELECT DISTINCT ITEMS FROM SSMA_SPLIT_F(@CRITERIO, ' ')) 
                      SPT
            WHERE   ID_AREA = @AREA
              AND   ID_HISTORICO NOT IN (SELECT ID_HISTORICO FROM SSMA_HISTORICO_T WHERE ESTADO = 'IN')
              AND   (
                          UPPER(DESCRIPCION) LIKE '%'+UPPER(SPT.ITEMS)+'%'
                      OR  UPPER(ID_ACTIVIDAD) LIKE '%'+UPPER(SPT.ITEMS)+'%'
                    )
          --
            UNION ALL
          --
            SELECT  'CND' 
                      AS TIPO,
                    CONVERT(VARCHAR(24),ID_CONDICIONANTE) 
                      AS IDENTIFICADOR,
                    DESCRIPCION  
                      AS DESCRIPCION,
                    CONVERT(VARCHAR(200),ID_CONDICIONANTE)
                      AS VINCULO,
                    'N/A'
                      AS FECHA 
            FROM    SSMA_CONDICIONANTES_T 
                      SCT,
                    @CRITS --(SELECT DISTINCT ITEMS FROM SSMA_SPLIT_F(@CRITERIO, ' ')) 
                      SPT
            WHERE   CLAVE_DOC IN (  SELECT CLAVE_DOC 
                                    FROM SSMA_DOCUMENTOS_T 
                                    WHERE ID_SECCION IN ( SELECT ID_SECCION 
                                                          FROM SSMA_SECCIONES_T 
                                                          WHERE ID_AREA = @AREA))
              AND   ID_HISTORICO NOT IN (SELECT ID_HISTORICO FROM SSMA_HISTORICO_T WHERE ESTADO = 'IN')
              AND   (
                          UPPER(DESCRIPCION) LIKE '%'+UPPER(SPT.ITEMS)+'%'
                      OR  UPPER(CLAVE_DOC) LIKE '%'+UPPER(SPT.ITEMS)+'%'
                      OR  UPPER(ID_CONDICIONANTE) LIKE '%'+UPPER(SPT.ITEMS)+'%'
                    )
        ) A
  GROUP BY  TIPO,
            IDENTIFICADOR,
            DESCRIPCION, 
            VINCULO
  HAVING COUNT(1) > @COINCIDENCIA/2
  ORDER BY COUNT(1) DESC, TIPO;
END;
GO

/****** Object: Stored Procedure [dbo].[SSMA_CAMBIAR_CONTRASENA_P]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_CAMBIAR_CONTRASENA_P]') AND type IN ('P', 'RF', 'PC')))
BEGIN
	DROP PROCEDURE [dbo].[SSMA_CAMBIAR_CONTRASENA_P]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SSMA_CAMBIAR_CONTRASENA_P]
  @LOGIN_USUARIO VARCHAR(25),
  @CONTRA_OLD VARCHAR(50),
  @CONTRA_NEW VARCHAR(50)
AS
BEGIN
  IF @CONTRA_NEW = @CONTRA_OLD BEGIN
    SELECT 0 AS RESULT;
  END
  ELSE BEGIN
    DECLARE @VALIDO int;
    
    SELECT  @VALIDO = COUNT(1) 
    FROM    SSMA_USUARIOS_T
    WHERE   [LOGIN] = @LOGIN_USUARIO
      AND   CONTRASENA = HashBytes('SHA1', CONVERT(nvarchar,@CONTRA_OLD));
    
    IF @VALIDO>0 BEGIN
      UPDATE  SSMA_USUARIOS_T
      SET     CONTRASENA = HashBytes('SHA1', CONVERT(nvarchar,@CONTRA_NEW))
      WHERE   [LOGIN] = @LOGIN_USUARIO
        AND   CONTRASENA = HashBytes('SHA1', CONVERT(nvarchar,@CONTRA_OLD));
      SELECT 1 AS RESULT;
    END;
    ELSE BEGIN
      SELECT 0 AS RESULT;
    END;
  END
END;
GO

/****** Object: Stored Procedure [dbo].[SSMA_CAMBIO_EDO_ACT_P]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_CAMBIO_EDO_ACT_P]') AND type IN ('P', 'RF', 'PC')))
BEGIN
	DROP PROCEDURE [dbo].[SSMA_CAMBIO_EDO_ACT_P]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SSMA_CAMBIO_EDO_ACT_P]
AS
BEGIN
  -- -----------------------
  -- RANDGO DE DIAS EMBEBIDO
  -- -----------------------
  DECLARE @RANGO_DIAS INT;
  SET @RANGO_DIAS = 30;
  --------------------------
  DECLARE @INICIO DATETIME;
  SET @INICIO = GETDATE();
  DECLARE @FIN DATETIME;
  SET @FIN = DATEADD(DAY,@RANGO_DIAS,@INICIO);
  
  UPDATE  SSMA_HISTORICO_T
  SET     ESTADO = 'PV'
  WHERE   ESTADO = 'PD'
    AND   ID_HISTORICO IN (
            SELECT  ID_HISTORICO
            FROM    SSMA_ACTIVIDADES_T
            WHERE   FECHA BETWEEN @INICIO AND @FIN
          );
  UPDATE  SSMA_HISTORICO_T
  SET     ESTADO = 'NP'
  WHERE   (ESTADO = 'PD' OR ESTADO = 'PV')
    AND   ID_HISTORICO IN (
            SELECT  ID_HISTORICO
            FROM    SSMA_ACTIVIDADES_T
            WHERE   FECHA < @INICIO
          );
END;
GO

/****** Object: Stored Procedure [dbo].[SSMA_LIMPIAR_HISTORICO_P]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_LIMPIAR_HISTORICO_P]') AND type IN ('P', 'RF', 'PC')))
BEGIN
	DROP PROCEDURE [dbo].[SSMA_LIMPIAR_HISTORICO_P]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SSMA_LIMPIAR_HISTORICO_P]
AS
BEGIN
  DECLARE @EXIST TABLE ( HIST INT );
  INSERT INTO @EXIST (HIST) (SELECT ID_HISTORICO FROM SSMA_ACTIVIDADES_T WHERE ID_HISTORICO IS NOT NULL)
  INSERT INTO @EXIST (HIST) (SELECT ID_HISTORICO FROM SSMA_CONDICIONANTES_T WHERE ID_HISTORICO IS NOT NULL)
  INSERT INTO @EXIST (HIST) (SELECT ID_HISTORICO FROM SSMA_DOCUMENTOS_T WHERE ID_HISTORICO IS NOT NULL)
  INSERT INTO @EXIST (HIST) (SELECT ID_HISTORICO FROM SSMA_SECCIONES_T WHERE ID_HISTORICO IS NOT NULL)
  INSERT INTO @EXIST (HIST) (SELECT ID_HISTORICO FROM SSMA_USUARIOS_T WHERE ID_HISTORICO IS NOT NULL)
  DELETE FROM SSMA_HISTORICO_T WHERE ID_HISTORICO NOT IN ( SELECT * FROM @EXIST );
END;
GO

/****** Object: Stored Procedure [dbo].[SSMA_PROX_FOLIO_P]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_PROX_FOLIO_P]') AND type IN ('P', 'RF', 'PC')))
BEGIN
	DROP PROCEDURE [dbo].[SSMA_PROX_FOLIO_P]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SSMA_PROX_FOLIO_P]
  @TIPO_DOC VARCHAR(4),
  @AREA INT
AS
BEGIN
  DECLARE @NEWF INT;
  --
  SELECT  RIGHT('0000' + CAST(( CASE COUNT(NEWF)
                                  WHEN 0 THEN 1
                                  ELSE NEWF + 1
                                END ) AS VARCHAR(4)),4) AS FOLIO
  FROM( SELECT 
          MAX(  CASE ISNUMERIC(FOLIO)
                  WHEN 1 THEN FOLIO
                  ELSE NULL
                END) AS NEWF
        FROM SSMA_DOCUMENTOS_T
        WHERE TIPO_DOC = @TIPO_DOC
          AND ID_SECCION IN (SELECT ID_SECCION FROM SSMA_SECCIONES_T WHERE ID_AREA = @AREA)
          ) A
  GROUP BY NEWF;
END;
GO

/****** Object: Stored Procedure [dbo].[SSMA_REPORTE_DOCS_P]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_REPORTE_DOCS_P]') AND type IN ('P', 'RF', 'PC')))
BEGIN
	DROP PROCEDURE [dbo].[SSMA_REPORTE_DOCS_P]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[SSMA_REPORTE_DOCS_P]
  @AREA INT,
  @REPORTE VARCHAR(100)
AS
BEGIN
  IF @REPORTE = 'Documentos' BEGIN
    SELECT  SDT.CLAVE_DOC                     
              AS "Clave",
            CASE WHEN LEN(SDT.DESCRIPCION) > 35  THEN CONVERT(VARCHAR(35),SDT.DESCRIPCION) + '...' ELSE SDT.DESCRIPCION END                  
              AS "Descripcion",
            CASE SDT.TIPO_DOC
              WHEN 'AUTO' THEN 'Autoridad'
              WHEN 'CMOC' THEN 'Cementos'
              WHEN 'OTRO' THEN 'Otros'
              WHEN 'CUMP' THEN 'Cumplimiento'
            END                               
              AS "Tipo",
            ( SELECT CASE WHEN LEN(NOMBRE) > 35  THEN CONVERT(VARCHAR(35),NOMBRE) + '...' ELSE NOMBRE END + '(' + ABREVIACION + ')'
              FROM SSMA_SECCIONES_T SST
              WHERE SST.ID_SECCION = SDT.ID_SECCION )
              AS "Seccion",
            SDT.LUGAR_FISICO 
              AS "Lugar Fisico",
            ( SELECT SUT.NOMBRE + ' ' + SUT.APELLIDOS + ' (' + SUT.[LOGIN] + ')'
              FROM SSMA_USUARIOS_T SUT
              WHERE SUT.[LOGIN] = SHT.USUARIO_CREA )
              AS "Creado Por",
            SHT.FECHA_CREA
              AS "Creado En",
            ( SELECT SUT.NOMBRE + ' ' + SUT.APELLIDOS + ' (' + SUT.[LOGIN] + ')'
              FROM SSMA_USUARIOS_T SUT
              WHERE SUT.[LOGIN] = SHT.USUARIO_MOD )
              AS "Modificado Por", 
            SHT.FECHA_MOD
              AS "Modificado En",
            CASE SHT.ESTADO
              WHEN 'CR' THEN 'Creado'
              WHEN 'ED' THEN 'Editado'
            END
              AS "Estado"
    FROM    SSMA_HISTORICO_T SHT,
            SSMA_DOCUMENTOS_T SDT
    WHERE   SHT.ID_HISTORICO = SDT.ID_HISTORICO
      AND   SHT.ESTADO != 'IN'
      AND   SDT.ID_SECCION IN (SELECT SDT.ID_SECCION FROM SSMA_SECCIONES_T WHERE ID_AREA = @AREA);
  END;
END;
GO

/****** Object: Stored Procedure [dbo].[SSMA_RESET_CONTRASENA_P]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_RESET_CONTRASENA_P]') AND type IN ('P', 'RF', 'PC')))
BEGIN
	DROP PROCEDURE [dbo].[SSMA_RESET_CONTRASENA_P]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SSMA_RESET_CONTRASENA_P]
  @LOGIN_USUARIO VARCHAR(25)
AS
BEGIN
  DECLARE @RAND_PASS VARCHAR(8);
  SELECT @RAND_PASS = CONTRA FROM SSMA_RANDOM_CONTRASENA_F();
  UPDATE  SSMA_USUARIOS_T
  SET     CONTRASENA = HashBytes('SHA1', CONVERT(nvarchar,@RAND_PASS))
  WHERE   [LOGIN] = @LOGIN_USUARIO;
  SELECT @RAND_PASS;
END
GO

/****** Object: Stored Procedure [dbo].[SSMA_TRUNC_ACTCONDOC_P]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_TRUNC_ACTCONDOC_P]') AND type IN ('P', 'RF', 'PC')))
BEGIN
	DROP PROCEDURE [dbo].[SSMA_TRUNC_ACTCONDOC_P]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SSMA_TRUNC_ACTCONDOC_P]
AS
BEGIN
  DECLARE @MAX INT;
  -- BORRA REGISTROS BASURA
  DELETE FROM SSMA_ACTIVIDADES_T;
  DELETE FROM SSMA_CONDICIONANTES_T;
  DELETE FROM SSMA_DOCUMENTOS_T;
  -- DELETE FROM SSMA_SECCIONES_T
  -- DELETE FROM SSMA_AREAS_T WHERE ID_AREA > 1
  --
  EXEC SSMA_LIMPIAR_HISTORICO_P; 
  --
  SELECT @MAX = CASE COUNT(ID_ACTIVIDAD) WHEN 0 THEN 0 ELSE MAX(ID_ACTIVIDAD) END FROM SSMA_ACTIVIDADES_T;
  DBCC CHECKIDENT (SSMA_ACTIVIDADES_T,RESEED,@MAX);
  --
  SELECT @MAX = CASE COUNT(ID_AREA) WHEN 0 THEN 0 ELSE MAX(ID_AREA) END FROM SSMA_AREAS_T;
  DBCC CHECKIDENT (SSMA_AREAS_T,RESEED,@MAX);
  --
  SELECT @MAX = CASE COUNT(ID_CONDICIONANTE) WHEN 0 THEN 0 ELSE MAX(ID_CONDICIONANTE) END FROM SSMA_CONDICIONANTES_T;
  DBCC CHECKIDENT (SSMA_CONDICIONANTES_T,RESEED,@MAX);
  --
  SELECT @MAX = CASE COUNT(ID_HISTORICO) WHEN 0 THEN 0 ELSE MAX(ID_HISTORICO) END FROM SSMA_HISTORICO_T;
  DBCC CHECKIDENT (SSMA_HISTORICO_T,RESEED,@MAX);
  --
  SELECT @MAX = CASE COUNT(ID_PLANTA) WHEN 0 THEN 0 ELSE MAX(ID_PLANTA) END FROM SSMA_PLANTAS_T;
  DBCC CHECKIDENT (SSMA_PLANTAS_T,RESEED,@MAX);
  --
  SELECT @MAX = CASE COUNT(ID_SECCION) WHEN 0 THEN 0 ELSE MAX(ID_SECCION) END FROM SSMA_SECCIONES_T;
  DBCC CHECKIDENT (SSMA_SECCIONES_T,RESEED,@MAX);
END
GO

/****** Object: Stored Procedure [dbo].[SSMA_VALIDA_USUARIO_P]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSMA_VALIDA_USUARIO_P]') AND type IN ('P', 'RF', 'PC')))
BEGIN
	DROP PROCEDURE [dbo].[SSMA_VALIDA_USUARIO_P]
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SSMA_VALIDA_USUARIO_P]
  @LOGIN VARCHAR (50),
  @CONTRASENA VARCHAR(50)
AS
BEGIN
    SELECT  *
    FROM    SSMA_USUARIOS_T
    WHERE   CONTRASENA = HashBytes('SHA1', CONVERT(nvarchar,@CONTRASENA))
    AND     UPPER([LOGIN]) = UPPER(@LOGIN);
END;
GO

/****** Object: Check Constraint [CK__SSMA_CONDICIONAN__25869641]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CK__SSMA_CONDICIONAN__25869641]') AND type ='C'))
BEGIN
	ALTER TABLE [dbo].[SSMA_CONDICIONANTES_T]
	DROP CONSTRAINT [CK__SSMA_CONDICIONAN__25869641]
END

GO

ALTER TABLE [dbo].[SSMA_CONDICIONANTES_T]
 ADD CONSTRAINT [CK__SSMA_CONDICIONAN__25869641] CHECK (([DURACION] IS NULL AND [PERIODICIDAD] IS NULL OR [DURACION] IS NOT NULL AND [PERIODICIDAD] IS NOT NULL))
GO

/****** Object: Check Constraint [CK__SSMA_DOCU__TIPO___3B40CD36]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CK__SSMA_DOCU__TIPO___3B40CD36]') AND type ='C'))
BEGIN
	ALTER TABLE [dbo].[SSMA_DOCUMENTOS_T]
	DROP CONSTRAINT [CK__SSMA_DOCU__TIPO___3B40CD36]
END

GO

ALTER TABLE [dbo].[SSMA_DOCUMENTOS_T]
 ADD CONSTRAINT [CK__SSMA_DOCU__TIPO___3B40CD36] CHECK (([TIPO_DOC]='OTRO' OR [TIPO_DOC]='CMOC' OR [TIPO_DOC]='AUTO' OR [TIPO_DOC]='CUMP'))
GO

/****** Object: Check Constraint [CK__SSMA_HIST__ESTAD__14E61A24]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CK__SSMA_HIST__ESTAD__14E61A24]') AND type ='C'))
BEGIN
	ALTER TABLE [dbo].[SSMA_HISTORICO_T]
	DROP CONSTRAINT [CK__SSMA_HIST__ESTAD__14E61A24]
END

GO

ALTER TABLE [dbo].[SSMA_HISTORICO_T]
 ADD CONSTRAINT [CK__SSMA_HIST__ESTAD__14E61A24] CHECK (([ESTADO]='NP' OR [ESTADO]='PD' OR [ESTADO]='IN' OR [ESTADO]='ED' OR [ESTADO]='AB' OR [ESTADO]='OK' OR [ESTADO]='CR' OR [ESTADO]='PV'))
GO

/****** Object: Primary Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_ACTIVIDADES_T]
 ADD PRIMARY KEY ([ID_ACTIVIDAD] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Unique Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_ACTIVIDADES_T]
 ADD UNIQUE ([ID_HISTORICO] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Primary Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_AREAS_T]
 ADD PRIMARY KEY ([ID_AREA] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Primary Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_CONDICIONANTES_T]
 ADD PRIMARY KEY ([ID_CONDICIONANTE] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Unique Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_CONDICIONANTES_T]
 ADD UNIQUE ([ID_HISTORICO] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Primary Key [PK__SSMA_DOCUMENTOS___24927208]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PK__SSMA_DOCUMENTOS___24927208]') AND type IN ('PK', 'UQ')))
BEGIN
	ALTER TABLE [dbo].[SSMA_DOCUMENTOS_T]
	DROP CONSTRAINT [PK__SSMA_DOCUMENTOS___24927208]
END

GO

ALTER TABLE [dbo].[SSMA_DOCUMENTOS_T]
 ADD CONSTRAINT [PK__SSMA_DOCUMENTOS___24927208] PRIMARY KEY ([CLAVE_DOC] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Unique Key [UQ__SSMA_DOCUMENTOS___2A4B4B5E]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UQ__SSMA_DOCUMENTOS___2A4B4B5E]') AND type IN ('PK', 'UQ')))
BEGIN
	ALTER TABLE [dbo].[SSMA_DOCUMENTOS_T]
	DROP CONSTRAINT [UQ__SSMA_DOCUMENTOS___2A4B4B5E]
END

GO

ALTER TABLE [dbo].[SSMA_DOCUMENTOS_T]
 ADD CONSTRAINT [UQ__SSMA_DOCUMENTOS___2A4B4B5E] UNIQUE ([CLAVE_DOC] ASC, [ID_HISTORICO] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Primary Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_HISTORICO_T]
 ADD PRIMARY KEY ([ID_HISTORICO] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Primary Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_LISTA_VALORES_T]
 ADD PRIMARY KEY ([ID_VALOR] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Primary Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_PLANTAS_T]
 ADD PRIMARY KEY ([ID_PLANTA] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Unique Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_PLANTAS_T]
 ADD UNIQUE ([ABREVIACION] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Primary Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_SECCIONES_T]
 ADD PRIMARY KEY ([ID_SECCION] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Unique Key [UQ__SSMA_SECCIONES_T__56B3DD81]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UQ__SSMA_SECCIONES_T__56B3DD81]') AND type IN ('PK', 'UQ')))
BEGIN
	ALTER TABLE [dbo].[SSMA_SECCIONES_T]
	DROP CONSTRAINT [UQ__SSMA_SECCIONES_T__56B3DD81]
END

GO

ALTER TABLE [dbo].[SSMA_SECCIONES_T]
 ADD CONSTRAINT [UQ__SSMA_SECCIONES_T__56B3DD81] UNIQUE ([ID_HISTORICO] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Primary Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_USUARIOS_T]
 ADD PRIMARY KEY ([LOGIN] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Unique Key [UQ__SSMA_USUARIOS_T__57A801BA]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UQ__SSMA_USUARIOS_T__57A801BA]') AND type IN ('PK', 'UQ')))
BEGIN
	ALTER TABLE [dbo].[SSMA_USUARIOS_T]
	DROP CONSTRAINT [UQ__SSMA_USUARIOS_T__57A801BA]
END

GO

ALTER TABLE [dbo].[SSMA_USUARIOS_T]
 ADD CONSTRAINT [UQ__SSMA_USUARIOS_T__57A801BA] UNIQUE ([LOGIN] ASC, [ID_HISTORICO] ASC) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

/****** Object: Foreign Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_ACTIVIDADES_T]
 ADD FOREIGN KEY ([CLAVE_DOC]) 
		REFERENCES [dbo].[SSMA_DOCUMENTOS_T] ([CLAVE_DOC]) 
GO

/****** Object: Foreign Key [FK__SSMA_ACTI__ID_AR__3F115E1A]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK__SSMA_ACTI__ID_AR__3F115E1A]') AND type ='F'))
BEGIN
	ALTER TABLE [dbo].[SSMA_ACTIVIDADES_T]
	DROP CONSTRAINT [FK__SSMA_ACTI__ID_AR__3F115E1A]
END

GO

ALTER TABLE [dbo].[SSMA_ACTIVIDADES_T]
 ADD CONSTRAINT [FK__SSMA_ACTI__ID_AR__3F115E1A] FOREIGN KEY ([ID_AREA]) 
		REFERENCES [dbo].[SSMA_AREAS_T] ([ID_AREA])  ON DELETE CASCADE ON UPDATE CASCADE
GO

/****** Object: Foreign Key [FK__SSMA_ACTI__ID_CO__15DA3E5D]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK__SSMA_ACTI__ID_CO__15DA3E5D]') AND type ='F'))
BEGIN
	ALTER TABLE [dbo].[SSMA_ACTIVIDADES_T]
	DROP CONSTRAINT [FK__SSMA_ACTI__ID_CO__15DA3E5D]
END

GO

ALTER TABLE [dbo].[SSMA_ACTIVIDADES_T]
 ADD CONSTRAINT [FK__SSMA_ACTI__ID_CO__15DA3E5D] FOREIGN KEY ([ID_CONDICIONANTE]) 
		REFERENCES [dbo].[SSMA_CONDICIONANTES_T] ([ID_CONDICIONANTE])  ON DELETE CASCADE ON UPDATE CASCADE
GO

/****** Object: Foreign Key [FK__SSMA_ACTI__ID_HI__40058253]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK__SSMA_ACTI__ID_HI__40058253]') AND type ='F'))
BEGIN
	ALTER TABLE [dbo].[SSMA_ACTIVIDADES_T]
	DROP CONSTRAINT [FK__SSMA_ACTI__ID_HI__40058253]
END

GO

ALTER TABLE [dbo].[SSMA_ACTIVIDADES_T]
 ADD CONSTRAINT [FK__SSMA_ACTI__ID_HI__40058253] FOREIGN KEY ([ID_HISTORICO]) 
		REFERENCES [dbo].[SSMA_HISTORICO_T] ([ID_HISTORICO])  ON DELETE CASCADE ON UPDATE CASCADE
GO

/****** Object: Foreign Key [FK__SSMA_AREA__ID_PL__2645B050]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK__SSMA_AREA__ID_PL__2645B050]') AND type ='F'))
BEGIN
	ALTER TABLE [dbo].[SSMA_AREAS_T]
	DROP CONSTRAINT [FK__SSMA_AREA__ID_PL__2645B050]
END

GO

ALTER TABLE [dbo].[SSMA_AREAS_T]
 ADD CONSTRAINT [FK__SSMA_AREA__ID_PL__2645B050] FOREIGN KEY ([ID_PLANTA]) 
		REFERENCES [dbo].[SSMA_PLANTAS_T] ([ID_PLANTA])  ON DELETE CASCADE ON UPDATE CASCADE
GO

/****** Object: Foreign Key [FK__SSMA_COND__CLAVE__2704CA5F]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK__SSMA_COND__CLAVE__2704CA5F]') AND type ='F'))
BEGIN
	ALTER TABLE [dbo].[SSMA_CONDICIONANTES_T]
	DROP CONSTRAINT [FK__SSMA_COND__CLAVE__2704CA5F]
END

GO

ALTER TABLE [dbo].[SSMA_CONDICIONANTES_T]
 ADD CONSTRAINT [FK__SSMA_COND__CLAVE__2704CA5F] FOREIGN KEY ([CLAVE_DOC]) 
		REFERENCES [dbo].[SSMA_DOCUMENTOS_T] ([CLAVE_DOC])  ON DELETE CASCADE ON UPDATE CASCADE
GO

/****** Object: Foreign Key [FK__SSMA_COND__ID_HI__489AC854]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK__SSMA_COND__ID_HI__489AC854]') AND type ='F'))
BEGIN
	ALTER TABLE [dbo].[SSMA_CONDICIONANTES_T]
	DROP CONSTRAINT [FK__SSMA_COND__ID_HI__489AC854]
END

GO

ALTER TABLE [dbo].[SSMA_CONDICIONANTES_T]
 ADD CONSTRAINT [FK__SSMA_COND__ID_HI__489AC854] FOREIGN KEY ([ID_HISTORICO]) 
		REFERENCES [dbo].[SSMA_HISTORICO_T] ([ID_HISTORICO]) 
GO

/****** Object: Foreign Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_DOCUMENTOS_T]
 ADD FOREIGN KEY ([ID_HISTORICO]) 
		REFERENCES [dbo].[SSMA_HISTORICO_T] ([ID_HISTORICO]) 
GO

/****** Object: Foreign Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_DOCUMENTOS_T]
 ADD FOREIGN KEY ([ID_SECCION]) 
		REFERENCES [dbo].[SSMA_SECCIONES_T] ([ID_SECCION]) 
GO

/****** Object: Foreign Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_DOCUMENTOS_T]
 ADD FOREIGN KEY ([PRECEDENTE]) 
		REFERENCES [dbo].[SSMA_DOCUMENTOS_T] ([CLAVE_DOC]) 
GO

/****** Object: Foreign Key [FK__SSMA_HIST__USUAR__498EEC8D]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK__SSMA_HIST__USUAR__498EEC8D]') AND type ='F'))
BEGIN
	ALTER TABLE [dbo].[SSMA_HISTORICO_T]
	DROP CONSTRAINT [FK__SSMA_HIST__USUAR__498EEC8D]
END

GO

ALTER TABLE [dbo].[SSMA_HISTORICO_T]
 ADD CONSTRAINT [FK__SSMA_HIST__USUAR__498EEC8D] FOREIGN KEY ([USUARIO_CREA]) 
		REFERENCES [dbo].[SSMA_USUARIOS_T] ([LOGIN]) 
GO

/****** Object: Foreign Key [FK__SSMA_HIST__USUAR__4A8310C6]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK__SSMA_HIST__USUAR__4A8310C6]') AND type ='F'))
BEGIN
	ALTER TABLE [dbo].[SSMA_HISTORICO_T]
	DROP CONSTRAINT [FK__SSMA_HIST__USUAR__4A8310C6]
END

GO

ALTER TABLE [dbo].[SSMA_HISTORICO_T]
 ADD CONSTRAINT [FK__SSMA_HIST__USUAR__4A8310C6] FOREIGN KEY ([USUARIO_MOD]) 
		REFERENCES [dbo].[SSMA_USUARIOS_T] ([LOGIN]) 
GO

/****** Object: Foreign Key [FK__SSMA_SECC__ID_AR__30C33EC3]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK__SSMA_SECC__ID_AR__30C33EC3]') AND type ='F'))
BEGIN
	ALTER TABLE [dbo].[SSMA_SECCIONES_T]
	DROP CONSTRAINT [FK__SSMA_SECC__ID_AR__30C33EC3]
END

GO

ALTER TABLE [dbo].[SSMA_SECCIONES_T]
 ADD CONSTRAINT [FK__SSMA_SECC__ID_AR__30C33EC3] FOREIGN KEY ([ID_AREA]) 
		REFERENCES [dbo].[SSMA_AREAS_T] ([ID_AREA])  ON DELETE CASCADE ON UPDATE CASCADE
GO

/****** Object: Foreign Key [FK__SSMA_SECC__ID_HI__31B762FC]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK__SSMA_SECC__ID_HI__31B762FC]') AND type ='F'))
BEGIN
	ALTER TABLE [dbo].[SSMA_SECCIONES_T]
	DROP CONSTRAINT [FK__SSMA_SECC__ID_HI__31B762FC]
END

GO

ALTER TABLE [dbo].[SSMA_SECCIONES_T]
 ADD CONSTRAINT [FK__SSMA_SECC__ID_HI__31B762FC] FOREIGN KEY ([ID_HISTORICO]) 
		REFERENCES [dbo].[SSMA_HISTORICO_T] ([ID_HISTORICO])  ON DELETE CASCADE ON UPDATE CASCADE
GO

/****** Object: Foreign Key    Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

ALTER TABLE [dbo].[SSMA_SECCIONES_T]
 ADD FOREIGN KEY ([PADRE]) 
		REFERENCES [dbo].[SSMA_SECCIONES_T] ([ID_SECCION]) 
GO

/****** Object: Foreign Key [FK__SSMA_USUA__ID_HI__2BFE89A6]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK__SSMA_USUA__ID_HI__2BFE89A6]') AND type ='F'))
BEGIN
	ALTER TABLE [dbo].[SSMA_USUARIOS_T]
	DROP CONSTRAINT [FK__SSMA_USUA__ID_HI__2BFE89A6]
END

GO

ALTER TABLE [dbo].[SSMA_USUARIOS_T]
 ADD CONSTRAINT [FK__SSMA_USUA__ID_HI__2BFE89A6] FOREIGN KEY ([ID_HISTORICO]) 
		REFERENCES [dbo].[SSMA_HISTORICO_T] ([ID_HISTORICO])  ON DELETE CASCADE ON UPDATE CASCADE
GO

/****** Object: Foreign Key [FK__SSMA_USUAR__AREA__2B0A656D]   Script Date: 22/05/2013 12:17:11 p.m. ******/
GO

IF (EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK__SSMA_USUAR__AREA__2B0A656D]') AND type ='F'))
BEGIN
	ALTER TABLE [dbo].[SSMA_USUARIOS_T]
	DROP CONSTRAINT [FK__SSMA_USUAR__AREA__2B0A656D]
END

GO

ALTER TABLE [dbo].[SSMA_USUARIOS_T]
 ADD CONSTRAINT [FK__SSMA_USUAR__AREA__2B0A656D] FOREIGN KEY ([ID_AREA]) 
		REFERENCES [dbo].[SSMA_AREAS_T] ([ID_AREA])  ON DELETE CASCADE ON UPDATE CASCADE
GO